package bid.dao.impl;

import bid.dao.ProjectDao;
import bid.entity.Department;
import bid.entity.*;
import bid.util.DBUtil;
import bid.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProjectDaoImpl implements ProjectDao {
    @Override
    public int add(Project p) {
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        String sql = "INSERT INTO project" +
                " (projectName, projectCategory, professorNum, isEvaluated, prochaseMode, departmentId, budget, " +
                "announceDate, openDate, address, dealDate, url, dealCompany, settlePrice, linkMan, mobliePhone, " +
                "projectFilePath, createBy, createDate, department, updateBy, updateDate, registerTime, " +
                "registerAddress, committee, professional, status) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?)";
        try {
            int index = 1;
            st = con.prepareStatement(sql);//sql参数
            //填充
            st.setString(index++, p.getProjectName());
            st.setString(index++, p.getProjectCategory());
            st.setInt(index++, p.getProfessorNum());
            st.setString(index++, p.getIsEvaluated());
            st.setString(index++, p.getProchaseMode());
            st.setLong(index++, p.getDepartmentId());
            st.setDouble(index++, p.getBudget());
            st.setDate(index++, p.getAnnounceDate() == null ? null : new java.sql.Date(p.getAnnounceDate().getTime()));
            st.setDate(index++, p.getOpenDate() == null ? null : new java.sql.Date(p.getOpenDate().getTime()));
            st.setString(index++, p.getAddress());
            st.setDate(index++, new java.sql.Date(p.getDealDate().getTime()));
            st.setString(index++, p.getUrl());
            st.setString(index++, p.getDealCompany());
            st.setDouble(index++, p.getSettlePrice());
            st.setString(index++, p.getLinkMan());
            st.setString(index++, p.getMobliePhone());
            st.setString(index++, p.getProjectFilePath());
            st.setString(index++, p.getCreateBy());
            st.setDate(index++, p.getCreateDate() == null ? null : new java.sql.Date(p.getCreateDate().getTime()));
            st.setString(index++, p.getDepartment());
            st.setString(index++, p.getUpdateBy());
            st.setDate(index++, p.getUpdateDate() == null ? null : new java.sql.Date(p.getUpdateDate().getTime()));
            st.setDate(index++, p.getRegisterTime() == null ? null : new java.sql.Date(p.getRegisterTime().getTime()));
            st.setString(index++, p.getRegisterAddress());
            st.setLong(index++, p.getCommittee());
            st.setString(index++, p.getProfessional());
            st.setLong(index, p.getStatus());
            st.executeUpdate();
            int r = st.executeUpdate();
            return r;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st, con);
        }
        return 0;

    }

    @Override
    public int update(Project p) {
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        String sql = "update project set " +
                "projectName=?, projectCategory=?, professorNum=?, isEvaluated=?, prochaseMode=?, departmentId=?, " +
                "budget=?, announceDate=?, openDate=?, address=?, dealDate=?, url=?, dealCompany=?, settlePrice=?, " +
                "linkMan=?, mobliePhone=?, projectFilePath=?, createBy=?, createDate=?, department=?, updateBy=?, " +
                "updateDate=?, registerTime=?, registerAddress=?, committee=?, professional=?, status=? " +
                "where id = ?";
        System.out.println(p.getId());
        try {
            //sql参数
            st = con.prepareStatement(sql);
            //填充占位符
            st.setString(1, p.getProjectName());
            st.setString(2, p.getProjectCategory());
            st.setInt(3, p.getProfessorNum());
            st.setString(4, p.getIsEvaluated());
            st.setString(5, p.getProchaseMode());
            st.setLong(6, p.getDepartmentId());
            st.setDouble(7, p.getBudget());
            st.setDate(8, p.getAnnounceDate() == null ? null : new java.sql.Date(p.getAnnounceDate().getTime()));
            st.setDate(9, p.getOpenDate() == null ? null : new java.sql.Date(p.getOpenDate().getTime()));
            st.setString(10, p.getAddress());
            st.setDate(11, new java.sql.Date(p.getDealDate().getTime()));
            st.setString(12, p.getUrl());
            st.setString(13, p.getDealCompany());
            st.setDouble(14, p.getSettlePrice());
            st.setString(15, p.getLinkMan());
            st.setString(16, p.getMobliePhone());
            st.setString(17, p.getProjectFilePath());
            st.setString(18, p.getCreateBy());
            st.setDate(19, p.getCreateDate() == null ? null : new java.sql.Date(p.getCreateDate().getTime()));
            st.setString(20, p.getDepartment());
            st.setString(21, p.getUpdateBy());
            st.setDate(22, p.getUpdateDate() == null ? null : new java.sql.Date(p.getUpdateDate().getTime()));
            st.setDate(23, p.getRegisterTime() == null ? null : new java.sql.Date(p.getRegisterTime().getTime()));
            st.setString(24, p.getRegisterAddress());
            st.setLong(25, p.getCommittee());
            st.setString(26, p.getProfessional());
            st.setLong(27, p.getStatus());
            st.setLong(28, p.getId());
//            System.out.println(st.toString());
            System.out.println("--execute update action in project--");
            System.out.println(st + "\n");
            int r = st.executeUpdate();
            return r;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st, con);
        }
        return 0;
    }

    @Override
    public int del(long id) {
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        String sql = "delete FROM project where isEvaluated = 1 AND";
        sql += " id = ? ";

        try {
            //sql参数
            st = con.prepareStatement(sql);
            //填充占位符
            st.setLong(1, id);
            System.out.println("--execute del action in project--");
            System.out.println(sql);
            System.out.println(st + "\n");
            int r = st.executeUpdate();
            return r;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            System.out.println(sql);
            DBUtil.close(st, con);
        }
        return 0;
    }

    @Override
    public Project get(long id) {
        String sql = "select p.* ,(select departmentname from department where id=p.departmentId)as departmentName " +
                "from project p where id =?";

        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符
            st.setLong(1, id);
            rs = st.executeQuery();

            if (rs.next()) {
                Project p = new Project();
                p.setId(rs.getLong("id"));
                p.setProjectName(rs.getString("projectName"));
                p.setProjectCategory(rs.getString("projectCategory"));
                p.setProfessorNum(rs.getInt("professorNum"));
                p.setIsEvaluated(rs.getString("isEvaluated"));
                p.setProchaseMode(rs.getString("prochaseMode"));

                p.setDepartmentId(rs.getLong("departmentId"));
                p.setDepartmentName(rs.getString("departmentName"));

                p.setBudget(rs.getDouble("budget"));
                p.setAnnounceDate(rs.getDate("announceDate"));
                p.setOpenDate(rs.getDate("openDate"));
                p.setAddress(rs.getString("address"));
                p.setDealDate(rs.getDate("dealDate"));
                p.setUrl(rs.getString("url"));
                p.setDealCompany(rs.getString("dealCompany"));
                p.setSettlePrice(rs.getDouble("settlePrice"));
                p.setLinkMan(rs.getString("linkMan"));
                p.setMobliePhone(rs.getString("mobliePhone"));
                p.setProjectFilePath(rs.getString("projectFilePath"));
                p.setCreateBy(rs.getString("createBy"));
                p.setCreateDate(rs.getDate("createDate"));
                p.setDepartment(rs.getString("department"));
                p.setUpdateBy(rs.getString("updateBy"));
                p.setUpdateDate(rs.getDate("updateDate"));
                p.setRegisterTime(rs.getDate("registerTime"));
                p.setRegisterAddress(rs.getString("registerAddress"));
                p.setCommittee(rs.getLong("committee"));
                p.setProfessional(rs.getString("professional"));
                p.setStatus(rs.getLong("status"));
                return p;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs,st, con);
        }
        return null;
    }

    @Override
    public List<Professor> findProfsByProject(long id) {
        List<Professor> datas = new ArrayList<>();
        String sql = "select p.id,p.professorName,p.major,p.mobliePhone,r.bePresent" +
                " from professor p inner join randomname r on p.id=r.professorId where r.projectId=?";
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符
            st.setLong(1, id);
            rs = st.executeQuery();

            while (rs.next()) {
                Professor p = new Professor();
                p.setId(rs.getLong("id"));
                p.setProfessorName(rs.getString("professorName"));
                p.setMajor(rs.getString("major"));
                p.setMobliePhone(rs.getString("mobliePhone"));
                p.setBePresent(rs.getString("bePresent"));
                datas.add(p);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, con);
        }
        return datas;
    }


    @Override
    public List<Project> search(int pageNo, int pageSize, String projectName, String projectCategory,
                                String isEvaluated, String registerAddress) {
        String sql = "select id,projectName,projectCategory,professorNum,isEvaluated,prochaseMode,departmentId," +
                "budget,announceDate,openDate,address,dealDate,url," +
                "dealCompany,settlePrice,linkMan,mobliePhone,projectFilePath,createBy,createDate," +
                "(select departmentName from department where id=project.departmentId)as department," +
                "updateBy,updateDate,registerTime,registerAddress," +
                "committee,professional,status" +
                " from project where 1=1";
        List<Object> params = new ArrayList<>();

        //嘿嘿，随机应变，这里我把所有小于0的id都不查询
//        if (id > 0 ) {
//            sql += " AND id LIKE ?";
//            params.add("%" + id + "%");
//        }
        if (StringUtils.isNotBlank(projectName)) {
            sql += " AND projectName LIKE ?";
            params.add("%" + projectName + "%");
        }
        if (StringUtils.isNotBlank(projectCategory)) {
            sql += " AND projectCategory LIKE ?";
            params.add("%" + projectCategory + "%");
        }
        if (StringUtils.isNotBlank(isEvaluated)) {
            sql += " AND isEvaluated LIKE ?";
            params.add("%" + isEvaluated + "%");
        }
        if (StringUtils.isNotBlank(registerAddress)) {
            sql += " AND registerAddress LIKE ?";
            params.add("%" + registerAddress + "%");
        }
//        if (StringUtils.isNotBlank(status)) {
//            sql += " AND status LIKE ?";
//            params.add("%" + status + "%");
//        }
        sql += " LIMIT ?, ?";
        params.add((pageNo - 1) * pageSize);
        params.add(pageSize);
        List<Project> projects = new ArrayList<>();

        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            st = con.prepareStatement(sql);
            for (int i = 0; i < params.size(); i++) {
                st.setObject(i + 1, params.get(i));
            }
            System.out.println("--execute search(six) method in project--");
            rs = st.executeQuery();
            while (rs.next()) {
                Project p = new Project();
                p.setId(rs.getLong("id"));
                p.setProjectName(rs.getString("projectName"));
                p.setProjectCategory(rs.getString("projectCategory"));
                p.setProfessorNum(rs.getInt("professorNum"));
                p.setIsEvaluated(rs.getString("isEvaluated"));
                p.setProchaseMode(rs.getString("prochaseMode"));
                p.setDepartmentId(rs.getLong("departmentId"));
                p.setBudget(rs.getDouble("budget"));
                p.setAnnounceDate(rs.getDate("announceDate"));
                p.setOpenDate(rs.getDate("openDate"));
                p.setAddress(rs.getString("address"));
                p.setDealDate(rs.getDate("dealDate"));
                p.setUrl(rs.getString("url"));
                p.setDealCompany(rs.getString("dealCompany"));
                p.setSettlePrice(rs.getDouble("settlePrice"));
                p.setLinkMan(rs.getString("linkMan"));
                p.setMobliePhone(rs.getString("mobliePhone"));
                p.setProjectFilePath(rs.getString("projectFilePath"));
                p.setCreateBy(rs.getString("createBy"));
                p.setCreateDate(rs.getDate("createDate"));
                p.setDepartment(rs.getString("department"));
                p.setUpdateBy(rs.getString("updateBy"));
                p.setUpdateDate(rs.getDate("updateDate"));
                p.setRegisterTime(rs.getDate("registerTime"));
                p.setRegisterAddress(rs.getString("registerAddress"));
                p.setCommittee(rs.getLong("committee"));
                p.setProfessional(rs.getString("professional"));
                p.setStatus(rs.getLong("status"));
                projects.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, con);
        }
        return projects;
    }

    @Override
    public long searchTotal(String projectName, String projectCategory, String isEvaluated, String registerAddress) {
        String sql = "select count(*) from project where 1=1";
        List<Object> params = new ArrayList<>();
        //嘿嘿，随机应变，这里我把所有小于0的id都不查询
//        if (id > 0 ) {
//            sql += " AND id LIKE ?";
//            params.add("%" + id + "%");
//        }
        if (StringUtils.isNotBlank(projectName)) {
            sql += " AND projectName LIKE ?";
            params.add("%" + projectName + "%");
        }
        if (StringUtils.isNotBlank(projectCategory)) {
            sql += " AND projectCategory LIKE ?";
            params.add("%" + projectCategory + "%");
        }
        if (StringUtils.isNotBlank(isEvaluated)) {
            sql += " AND isEvaluated LIKE ?";
            params.add("%" + isEvaluated + "%");
        }
        if (StringUtils.isNotBlank(registerAddress)) {
            sql += " AND registerAddress LIKE ?";
            params.add("%" + registerAddress + "%");
        }

        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            st = con.prepareStatement(sql);
            for (int i = 0; i < params.size(); i++) {
                st.setObject(i + 1, params.get(i));
            }
            System.out.println("--execute search(four) method in project--");
            rs = st.executeQuery();
            if (rs.next()) {
                return rs.getLong(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, con);
        }
        return 0;
    }

    @Override
    public List<Project> findAll() {
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;
        List<Project> projects = new ArrayList<>();
        String sql = "select * from project";
        try {
            st = con.prepareStatement(sql);
            rs = st.executeQuery();
            System.out.println("--execute findAll() method in project--");
            while (rs.next()) {
                Project p = new Project();
                p.setId(rs.getLong("id"));
                p.setProjectName(rs.getString("projectName"));
                p.setProjectCategory(rs.getString("projectCategory"));
                p.setProfessorNum(rs.getInt("professorNum"));
                p.setIsEvaluated(rs.getString("isEvaluated"));
                p.setProchaseMode(rs.getString("prochaseMode"));
                p.setDepartmentId(rs.getLong("departmentId"));
                p.setBudget(rs.getDouble("budget"));
                p.setAnnounceDate(rs.getDate("announceDate"));
                p.setOpenDate(rs.getDate("openDate"));
                p.setAddress(rs.getString("address"));
                p.setDealDate(rs.getDate("dealDate"));
                p.setUrl(rs.getString("url"));
                p.setDealCompany(rs.getString("dealCompany"));
                p.setSettlePrice(rs.getDouble("settlePrice"));
                p.setLinkMan(rs.getString("linkMan"));
                p.setMobliePhone(rs.getString("mobliePhone"));
                p.setProjectFilePath(rs.getString("projectFilePath"));
                p.setCreateBy(rs.getString("createBy"));
                p.setCreateDate(rs.getDate("createDate"));
                p.setDepartment(rs.getString("department"));
                p.setUpdateBy(rs.getString("updateBy"));
                p.setUpdateDate(rs.getDate("updateDate"));
                p.setRegisterTime(rs.getDate("registerTime"));
                p.setRegisterAddress(rs.getString("registerAddress"));
                p.setCommittee(rs.getLong("committee"));
                p.setProfessional(rs.getString("professional"));
                p.setStatus(rs.getLong("status"));
                projects.add(p);
            }
            return projects;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, con);
        }
        return null;
    }


    @Override
    public Page<Project> page(int pageNo, int pageSize, String projectName, String projectCategory,
                              String isEvaluated, String registerAddress) {
        List<Project> list = this.search(pageNo, pageSize, projectName, projectCategory, isEvaluated, registerAddress);
        Long total = this.searchTotal(projectName, projectCategory, isEvaluated, registerAddress);
        System.out.println("--execute page() method in project--");
        return new Page<>(pageNo, pageSize, total, list);
    }
}
